package libraryAdministration;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class BookDao {
	//连接数据库	用的是Server
	Connection conn;
	public BookDao() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");//将驱动程序类加载到内存中
		String url="jdbc:mysql://localhost/library";
		String user="Caizi";
		String password="tang";
		conn=DriverManager.getConnection(url, user, password);
	}
	//添加书
	public int addBook(Book book) throws SQLException {
		String sql="INSERT INTO books (Name, Author, Price, Press) VALUES (?, ?, ?, ?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, book.getName());//接收到书名
		ps.setString(2, book.getAuthor());//接收到作者
		ps.setDouble(3, book.getPrice());//接收到价格
		ps.setString(4, book.getPress());//接收到出版社
		return ps.executeUpdate();
	}
	//删除书
	public int deleteBook(int id) throws SQLException {//通过ID删除书
		String sql="DELETE FROM books WHERE (ID=?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, id);
		return ps.executeUpdate();
	}
	//修改书
	public int modifyBook(int id,Book newbook) throws SQLException {
		String sql="UPDATE books SET Name=?, Author=?, Price=?, Press=? WHERE (id=?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, newbook.getName());
		ps.setString(2, newbook.getAuthor());
		ps.setDouble(3, newbook.getPrice());
		ps.setString(4, newbook.getPress());
		ps.setInt(5, id);//接收到修改书的ID
		return ps.executeUpdate();
	}
	//获得所有书的信息
	public ArrayList<Book> getBooks() throws SQLException{
		ArrayList<Book> books=new ArrayList<Book>();
		String sql="SELECT * FROM books";
		Statement st=conn.createStatement();
		ResultSet rs=st.executeQuery(sql);
		while(rs.next()) {
			Book book=new Book();
			book.setId(rs.getInt(1));
			book.setName(rs.getString(2));
			book.setAuthor(rs.getString(3));
			book.setPrice(rs.getDouble(4));
			book.setPress(rs.getString(5));
			books.add(book);//将获得的书的信息保存在books中
		}
		return books;
	}
	//通过书名查找图书
	public ArrayList<Book> getBooks(String bookName) throws SQLException {
		ArrayList<Book> books=new ArrayList<Book>();
		String sql="SELECT * FROM books WHERE (Name=?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, bookName);
		ResultSet rs=ps.executeQuery();
		Book book=new Book();
		while(rs.next()) {
			book.setId(rs.getInt(1));
			book.setName(rs.getString(2));
			book.setAuthor(rs.getString(3));
			book.setPrice(rs.getDouble(4));
			book.setPress(rs.getString(5));
			books.add(book);
		}
		
		return books;
	}
	//通过ID查找图书
	public Book getBookById(int id) throws SQLException {
		String sql="SELECT * FROM books WHERE (id=?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs=ps.executeQuery();
		Book book=new Book();
		while(rs.next()) {
			book.setId(rs.getInt(1));
			book.setName(rs.getString(2));
			book.setAuthor(rs.getString(3));
			book.setPrice(rs.getDouble(4));
			book.setPress(rs.getString(5));
		}
		return book;
	}
}












